﻿/*
 *  Copyright 2009 Alessandro Pilotti
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU Lesser General Public License as published by
 *  the Free Software Foundation; either version 2.1 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU Lesser General Public License for more details.
 *
 *  You should have received a copy of the GNU Lesser General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA 
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;

namespace AlexPilotti.L2SQLDataModel.SchemaInfo
{
    class MSSQL2KDBSchemaInfo : MSSQLBaseDBSchemaInfo
    {
        internal MSSQL2KDBSchemaInfo(string connStr)
            : base(connStr)
        {
        }

        protected override string GetCreateTempTableSql(string objectTypesSql)
        {
            return "select o.id as object_id, u.name as schema_name, o.name, o.type into #Objects " +
                    "from sysobjects o join sysusers u " + 
                    "on o.uid = u.uid " +
                    "where " +
                    "o.type IN (" + objectTypesSql + ") and " +
                    "OBJECTPROPERTY(id, 'IsMSShipped') = 0 " +
                    "order by o.type, u.name, o.name ";
        }

        protected override string GetParametersInfoSql()
        {
            return "SELECT t.schema_name AS schema_name, " +
                    "t.name AS [object_name], " +
                    "t.type AS [object_type], " +
                    "clmns.name AS parameter_name, " +
                    "clmns.colid AS ID, " +
                    "CAST(clmns.isoutparam AS bit) AS [IsOutputParameter], " +
                    "N'' AS [UserType], " +
                    "usrt.name AS DataType, " +
                    "sclmns.name AS DataTypeSchema, " + 
                    "ISNULL(baset.name, N'') AS SystemType, " +
                    "CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.length <> -1 THEN clmns.length/2 ELSE clmns.length END AS int) AS Length, " +
                    "ISNULL(CAST(clmns.prec AS int), 0) AS [NumericPrecision], " + 
                    "ISNULL(CAST(clmns.scale AS int), 0) AS [NumericScale] " + 
                    "FROM #Objects AS t INNER JOIN " +
                    "syscolumns AS clmns ON clmns.id = t.object_id LEFT OUTER JOIN " +
                    "systypes AS usrt ON usrt.xusertype = clmns.xusertype LEFT OUTER JOIN " +
                    "sysusers AS sclmns ON sclmns.uid = usrt.uid LEFT OUTER JOIN " +
                    "systypes AS baset ON  baset.xusertype = clmns.xtype " +
                    "where t.type in (" + procFuncTypesSql + ") " +
                    "order by " +
                    "t.schema_name, t.object_name, clmns.colid";
        }

        protected override string GetColumnsInfoSql()
        {
            return "SELECT t.schema_name AS schema_name, t.name AS [object_name], clmns.name AS Column_Name, clmns.colid AS ID, " +
                    "usrt.name AS DataType, sclmns.name AS DataTypeSchema, ISNULL(baset.name, N'') AS SystemType, "+
                    "CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.length <> -1 THEN clmns.length/2 ELSE clmns.length END AS int) AS Length, "+
                    "ISNULL(CAST(clmns.prec AS int), 0) AS [NumericPrecision], ISNULL(CAST(clmns.scale AS int), 0) AS [NumericScale], CAST(clmns.isnullable AS bit) AS [Nullable], " +
                    "CAST(COLUMNPROPERTY(t.object_id, clmns.name, 'IsIdentity') AS bit) AS [Identity], 1 AS [IdentitySeed], 1 AS [IdentityIncrement], " + 
                    "CAST(clmns.iscomputed AS bit) AS [Computed] " + 
                    "FROM #Objects AS t INNER JOIN " + 
                    "syscolumns AS clmns ON clmns.id = t.object_id LEFT OUTER JOIN " + 
                    "systypes AS usrt ON usrt.xusertype = clmns.xusertype LEFT OUTER JOIN " + 
                    "sysusers AS sclmns ON sclmns.uid = usrt.uid LEFT OUTER JOIN " + 
                    "systypes AS baset ON  baset.xusertype = clmns.xtype " +
                    "where t.type in (" + tableTypeSql + "," + viewTypeSql  + ") " + 
                    "order by " +
                    "t.schema_name, t.object_name, clmns.colid";
        }
    }
}
